﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using VIT.DataHelper;

namespace VIT.Provider
{
    public class FaqGroup : FaqGroup_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public FaqGroup()
        {
            this.Id = -1;
            this.ParentId = 0;
            this.LanguageId = string.Empty;
            this.Image = string.Empty;
            this.Title = string.Empty;
            this.Description = string.Empty;
            this.Orders = 50;
            this.Published = true;
        }
        #endregion

        #region List
        public DataTable GetAll(string FaqGrouplist="")
        {
            string strQuery = string.Empty;
            if (FaqGrouplist == "")
                strQuery = "select Id, Title, ParentId, Image, Description, Orders, Published from FaqGroup where LanguageID=@LanguageID and Deleted=0";
            else
                strQuery = "select Id, Title, ParentId, Image, Description, Orders, Published from FaqGroup where LanguageID=@LanguageID and Deleted=0 and Id in (" + FaqGrouplist + ")";
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return ExecuteDataset(false, strQuery, LanguageId).Tables[0];
        }

        /* If FaqGroup has children or has article then it can't be deleted */
        public bool checkForDel(int id)
        {
            string strQuery = "select count(Id) from FaqGroup where ParentId=@Id and Deleted=0";
            SqlParameter Id = new SqlParameter("@Id", id);
            int count = int.Parse(ExecuteScalar(false, strQuery, Id).ToString());
            if (count > 0) return false; // has child

            strQuery = "select count(Faq.Id) from Faq where FaqGroupId=@Id";
            count = int.Parse(ExecuteScalar(false, strQuery, Id).ToString());
            if (count > 0) return false; // has article
            return true;
        }

        public bool Delete(int delete, int id)
        {
            string strQuery = "update FaqGroup set Deleted=@Deleted, ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete==1)?user:"");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, D, User, DeleteByUser, Now)>0;            
        }

        public bool Publish(int publish, int id)
        {
            string strQuery = "update FaqGroup set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, P, User, Now) > 0;
        }

        public bool Order(string order, int id)
        {
            string strQuery = "update FaqGroup set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, User, Now) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select ParentId, Title, Image, Description, Orders, Published from FaqGroup where LanguageID=@LanguageId and Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["ParentId"].ToString()))
                        this.ParentId = (int)dt.Rows[0]["ParentId"];
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = -1;
        }

        public bool Exist()
        {
            string strQuery = "select Id from FaqGroup where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(false, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid)
        {
            string strQuery = "select Id, LanguageID from FaqGroup where Id=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(false, strQuery, Id, LanguageId) != null;
        }

        public int Save()
        {
            if (Exist())
            {
                Delete(0, this.Id);
                return Update();
            }
            else
            {
                this.Id = Insert();
                return this.Id;
            }
        }

        public int Insert()
        {
            string strQuery = "insert into FaqGroup(ParentId, LanguageId, Image, Title, Description, Orders, Published,  Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@ParentId, @LanguageId, @Image, @Title, @Description, @Orders, @Published, 0, @Now, @Now, @User, @User)";

            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, ParentId, LanguageId, Image, Title, Description, Orders, Published, User, Now);
        }

        public int Update()
        {
            string sqlCat = "update FaqGroup set ParentId=@ParentId, LanguageId=@LanguageId, Image=@Image, Title=@Title, Description=@Description, Orders=@Orders, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlCat, Id, ParentId, LanguageId, Image, Title, Description, Orders, Published, User, Now);
        }

        #endregion

        #region Trash
        public string getParentId(int delete, int id)
        {
            string strQuery = "select parent.Id from FaqGroup child, FaqGroup parent where child.Id=@Id and parent.Deleted=@Delete and parent.Id=child.ParentId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(false, strQuery, Id, d);
            return (obj != null) ? obj.ToString() : "";
        }

        public DataTable GetRecycleBin(string SortColumn)
        {
            string strQuery;
            if(SortColumn == "")
                strQuery = "select * from FaqGroup where Deleted=1";
            else
                strQuery = "select * from FaqGroup where Deleted=1 order by " + SortColumn;
            return ExecuteDataset(false, strQuery).Tables[0];
        }

        public bool Cleared(int id)
        {
            string strQuery = "delete from FaqGroup where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(false, strQuery, Id)>0;
        }

        public string GetImage()
        {
            string strQuery = "select Image from GroupTrash where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            object obj = ExecuteScalar(false, strQuery, Id);
            if (obj != null) return obj.ToString();
            return "";
        }
        #endregion
    }
}
